/**
 * Copyright (C) 2014 Luvina Software Company
 * UserDaoImpl.java, Dec 15, 2014, Nguyen Huu Luan LA11_AM
 *
 */
package net.luvina.manageuser.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import net.luvina.manageuser.dao.TblUserDao;
import net.luvina.manageuser.entities.TblUser;
import net.luvina.manageuser.entities.UserInfor;
import net.luvina.manageuser.utils.Common;

/**
 * DAO implements interface TblUserDao để thao tác với dữ liệu user.
 * 
 * @author luan
 * 
 */
public class TblUserDaoImpl extends BaseDaoImpl implements TblUserDao {

	/*
	 * (non-Javadoc)
	 * 
	 * @see manageuser.dao.UserDao#getUserByLoginId(java.lang.String,
	 * java.lang.String)
	 */
	public TblUser getUserByLoginNamePassword(String loginName, String password) {
		TblUser user = null;
		if (connectToDB()) {
			try {
				StringBuilder sqlCommand = new StringBuilder();

				// Tạo câu lệnh SQL
				sqlCommand
						.append("SELECT u.user_id, u.full_name, u.birthday, ");
				sqlCommand.append("u.email, u.tel ");
				sqlCommand.append("FROM tbl_user AS u ");
				sqlCommand.append("WHERE ");
				sqlCommand.append("u.login_name = ? ");
				sqlCommand.append(" AND ");
				sqlCommand.append("u.password = ? ");

				preparedStatement = connection.prepareStatement(sqlCommand
						.toString());

				// Gán giá trị cho các vị trí chờ.
				preparedStatement.setString(1, loginName);
				preparedStatement.setString(2, password);

				rs = preparedStatement.executeQuery();

				// Đọc dữ liệu thu được.
				if (rs != null) {
					while (rs.next()) {
						user = new TblUser();
						user.setUserId(rs.getInt("user_id"));
						user.setFullName(rs.getString("full_name"));
						user.setBirthday(rs.getString("birthday"));
						user.setEmail(rs.getString("email"));
						user.setTel(rs.getString("tel"));
						user.setLoginName(loginName);
						user.setPassword(password);
					}
					rs.close();
				}

			} catch (Exception e) {
				System.out.println("an exception occur: " + e.getMessage());
			}
			closeConnect();

		}
		return user;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see manageuser.dao.UserDao#getListUsers(manageuser.entities.TblUser,
	 * int, int)
	 */
	public List<UserInfor> getListUsers(int offset, int limit, int groupId,
			String fullName, String sortType, String sortByFullName,
			String sortByCodeLevel, String sortByEndDate) {
		List<UserInfor> lsUsers = new ArrayList<UserInfor>();
		if (connectToDB()) {
			try {
				StringBuilder sqlCommand = new StringBuilder();

				// Tạo Query SQL
				sqlCommand.append("SELECT ");
				sqlCommand
						.append("u.user_id, u.full_name, u.birthday, u.full_name_kana, ");
				sqlCommand
						.append("g.group_name, u.email, u.tel, j.start_date, ");
				sqlCommand.append("mj.name_level, j.end_date, j.total ");
				sqlCommand.append("FROM ( tbl_user AS u ");
				sqlCommand.append("INNER JOIN mst_group AS g ");
				sqlCommand.append("ON u.group_id = g.group_id ) ");
				sqlCommand.append("LEFT JOIN ");
				sqlCommand.append("(tbl_detail_user_japan AS j ");
				sqlCommand.append("INNER JOIN mst_japan AS mj ");
				sqlCommand.append("ON j.code_level = mj.code_level ) ");
				sqlCommand.append("ON u.user_id = j.user_id ");
				sqlCommand.append("WHERE u.rule = 1 ");

				// Gán điều kiện tìm kiếm.
				if (groupId > 0 && fullName.length() > 0) {
					sqlCommand.append("AND ");
					sqlCommand.append("u.group_id = ? ");
					sqlCommand.append("AND ");
					sqlCommand.append("u.full_name LIKE ? ");
				} else if (groupId > 0) {
					sqlCommand.append("AND ");
					sqlCommand.append("u.group_id = ? ");
				} else if (fullName.length() > 0) {
					sqlCommand.append("AND ");
					sqlCommand.append("u.full_name LIKE ? ");
				}

				/*
				 * 
				 * Gán điều kiện sắp xếp.
				 */
				if ("full_name".equals(sortType)) {
					sqlCommand.append(" ORDER BY u.full_name " + sortByFullName
							+ ", j.code_level " + sortByCodeLevel
							+ ", j.end_date " + sortByEndDate);
				} else if ("code_level".equals(sortType)) {
					sqlCommand.append(" ORDER BY j.code_level "
							+ sortByCodeLevel + ", u.full_name "
							+ sortByFullName + ", j.end_date " + sortByEndDate);
				} else if ("end_date".equals(sortType)) {
					sqlCommand.append(" ORDER BY j.end_date " + sortByEndDate
							+ ", u.full_name " + sortByFullName
							+ ", j.code_level " + sortByCodeLevel);
				}

				// Gán limit và offset.
				sqlCommand.append(" LIMIT ?, ? ");
				// Tạo câu lệnh SQL
				preparedStatement = connection.prepareStatement(sqlCommand
						.toString());

				// Gán giá trị cho các vị trí chờ.
				if (groupId > 0 && fullName.length() > 0) {
					preparedStatement.setInt(1, groupId);
					preparedStatement.setString(2, "%" + fullName + "%");
					preparedStatement.setInt(3, offset);
					preparedStatement.setInt(4, limit);
				} else if (groupId > 0) {
					preparedStatement.setInt(1, groupId);
					preparedStatement.setInt(2, offset);
					preparedStatement.setInt(3, limit);
				} else if (fullName.length() > 0) {
					preparedStatement.setString(1, "%" + fullName + "%");
					preparedStatement.setInt(2, offset);
					preparedStatement.setInt(3, limit);
				} else {
					preparedStatement.setInt(1, offset);
					preparedStatement.setInt(2, limit);
				}

				// lấy về kết quả.
				rs = preparedStatement.executeQuery();

				// Đọc kết quả.
				if (rs != null) {
					while (rs.next()) {
						// format lại ngày tháng theo đúng định dạng trước khi
						// trả về.
						String birthday = Common.formatDate(rs
								.getDate("birthday"));
						String endDate = "";
						Date tempDate = rs.getDate("end_date");
						if (tempDate != null) {
							endDate = Common.formatDate(tempDate);
						}

						UserInfor userInfor = new UserInfor();

						userInfor.setUserId(rs.getInt("user_id"));
						userInfor.setFullName(Common.escapeHTML(rs
								.getString("full_name")));
						userInfor.setBirthday(birthday);
						userInfor.setGroupName(rs.getString("group_name"));
						userInfor.setEmail(rs.getString("email"));
						userInfor.setTel(rs.getString("tel"));
						userInfor.setNameLevel(rs.getString("name_level"));
						userInfor.setEndDate(endDate);
						userInfor.setTotal(rs.getInt("total"));
						/*
						 * 
						 * 
						 * Set các thuộc tính để thao tác trên màn hình ADM003.
						 */
						userInfor.setFullNameKana(rs
								.getString("full_name_kana"));
						userInfor.setBirthdayADM003(Common.toArrayInteger(rs
								.getDate("birthday")));
						Date tempStartDate = rs.getDate("start_date");
						if (tempStartDate != null) {
							userInfor.setStartDateADM003(Common
									.toArrayInteger(tempStartDate));
							userInfor.setEndDateADM003(Common.toArrayInteger(rs
									.getDate("end_date")));
						}
						lsUsers.add(userInfor);

					}
					rs.close();
				}

			} catch (Exception e) {
				System.out.println("an exception occur: " + e.getMessage());
			}
			closeConnect();

		}
		return lsUsers;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see manageuser.dao.UserDao#getTotalUsers(manageuser.entities.TblUser)
	 */
	public int getTotalUsers(int groupId, String fullName) {
		int totalUsers = 0;
		if (connectToDB()) {
			try {
				StringBuilder sqlCommand = new StringBuilder();

				// Tạo câu truy vấn SQL
				sqlCommand.append("SELECT count(u.user_id) ");
				sqlCommand.append("AS total ");
				sqlCommand.append("FROM tbl_user AS u ");
				sqlCommand.append("WHERE u.rule = 1 ");

				// Gán điều kiện tìm kiếm.
				if (groupId > 0 && fullName.length() > 0) {
					sqlCommand.append("AND ");
					sqlCommand.append("u.group_id = ? ");
					sqlCommand.append("AND ");
					sqlCommand.append("u.full_name LIKE ? ");
				} else if (groupId > 0) {
					sqlCommand.append("AND ");
					sqlCommand.append("u.group_id = ? ");
				} else if (fullName.length() > 0) {
					sqlCommand.append("AND ");
					sqlCommand.append("u.full_name LIKE ? ");
				}

				// Tạo câu lệnh SQL.
				preparedStatement = connection.prepareStatement(sqlCommand
						.toString());

				// Gán giá trị cho các vị trí chờ.
				if (groupId > 0 && fullName.length() > 0) {
					preparedStatement.setInt(1, groupId);
					preparedStatement.setString(2, "%" + fullName + "%");
				} else if (groupId > 0) {
					preparedStatement.setInt(1, groupId);
				} else if (fullName.length() > 0) {
					preparedStatement.setString(1, "%" + fullName + "%");
				}

				// Chạy câu lệnh và lấy ra danh sách bản ghi.
				rs = preparedStatement.executeQuery();

				// Đọc danh sách bản ghi và trả về kết quả.
				if (rs != null) {
					while (rs.next()) {
						totalUsers = rs.getInt("total");
					}
					rs.close();
				}

			} catch (Exception e) {
				System.out.println("an exception occur: " + e.getMessage());
			}
			closeConnect();

		}
		return totalUsers;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * net.luvina.manageuser.dao.TblUserDao#getFieldAllUser(java.lang.String)
	 */
	public List<TblUser> getSpecificInfoOfAllUser() {
		List<TblUser> listInfo = new ArrayList<TblUser>();
		TblUser user = null;
		if (connectToDB()) {
			try {
				StringBuilder sqlCommand = new StringBuilder();
				sqlCommand
						.append("SELECT u.login_name, u.email FROM tbl_user AS u ");
				preparedStatement = connection.prepareStatement(sqlCommand
						.toString());
				// Chạy câu lệnh và lấy ra danh sách bản ghi.
				rs = preparedStatement.executeQuery();
				// Đọc danh sách bản ghi và trả về kết quả.
				if (rs != null) {
					while (rs.next()) {
						user = new TblUser();
						user.setLoginName(rs.getString("login_name"));
						user.setEmail(rs.getString("email"));
						listInfo.add(user);
					}
					rs.close();
				}

			} catch (Exception e) {
				System.out.println("an exception occur: " + e.getMessage());
			}
			closeConnect();

		}
		return listInfo;
	}

}
